package com.dagong.test;


import org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

public class JdbcPersonRun {

    @Test
    public void insertPerson() {        //加载驱动 建立连接 创建执行语句对象 执行insert语句 释放资源关闭连接
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://127.0.0.1:3306/myuse?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
            connection = DriverManager.getConnection(url, "root", "123456");

            PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO person VALUES(?,?,?,?)");

            int age=46;
            Person person=new Person("张三",age,new Date());
            //用于自定义年月日，去掉后将使用今天的日期
            person.setYear(person.getBirthday().getYear()-age);
            person.setMonth(8);
            person.setDate(18);

            preparedStatement.setString(1, UUID.randomUUID().toString());
            preparedStatement.setString(2, person.getName());
            preparedStatement.setInt(3, person.getAge());
            java.sql.Date td=new java.sql.Date(person.getTime());
            preparedStatement.setDate(4, td);

            int i = preparedStatement.executeUpdate();
            System.out.println("插入成功：" + i);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Test
    public void queryData(){        //加载驱动 建立连接 创建执行语句对象 执行select语句 释放资源关闭连接
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://127.0.0.1:3306/myuse?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
            connection = DriverManager.getConnection(url, "root", "123456");

            PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM person WHERE name LIKE ?");

            preparedStatement.setString(1, "%三%");
            ResultSet rs = preparedStatement.executeQuery();

            List<Person> list= new ArrayList<>();
            while(rs.next()) {
                String id=rs.getString(1);
                String name=rs.getString(2);
                Integer age=rs.getInt(3);
                java.sql.Date date=rs.getDate(4);

                Person person = new Person();
                person.setName(name);
                person.setAge(age);
                person.setBirthday(date);

                list.add(person);
                System.out.println(id+"_"+name+"_"+age+"_"+date);
            }
            System.out.println(list.toString());
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Test
    public void updatePerson() {        //加载驱动 建立连接 创建执行语句对象 执行update语句 释放资源关闭连接
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://127.0.0.1:3306/myuse?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
            connection = DriverManager.getConnection(url, "root", "123456");

            PreparedStatement preparedStatement = connection.prepareStatement("UPDATE person SET name=? WHERE name LIKE ?");

            preparedStatement.setString(1, "德莱文");
            preparedStatement.setString(2, "%三%");

            int i = preparedStatement.executeUpdate();
            System.out.println("更新成功：" + i);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Test
    public void deletePerson() {        //加载驱动 建立连接 创建执行语句对象 执行delete语句 释放资源关闭连接
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://127.0.0.1:3306/myuse?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
            connection = DriverManager.getConnection(url, "root", "123456");

            PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM person WHERE name LIKE ?");

            preparedStatement.setString(1, "德莱文");

            int i = preparedStatement.executeUpdate();
            System.out.println("删除成功：" + i);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}
